package group32.server;

import group32.client.CircleService;
import group32.client.SearchService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;

@SuppressWarnings("serial")
public class CircleServiceImpl extends RemoteServiceServlet implements CircleService{
Connection connection;
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	/**
	 * Constructor -- initialize our source tables and the object
	 */
	public CircleServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String serverName = "fling.seas.upenn.edu:3306";
			String database = "vdurey";
			String url = "jdbc:mysql://" + serverName + "/" + database;
			//url = "jdbc:mysql://fling.seas.upenn.edu:3306/vdurey";
			String user = "vdurey";
			String password = "cis550azerty";
			
			connection = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			//System.exit(1);
		} catch (SQLException e) {
			e.printStackTrace();
			//System.exit(1);
		}
	}
	
	/**
	 * Returns a relation output by computing a query
	 */
	@Override
	public ArrayList<String> getRelation(int userID) throws IllegalArgumentException {
		
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select name from circles where cid in (select circleid from usercircles where userID="+userID+");")){
				ResultSet rs = statement.getResultSet();
				//JSONObject json = new JSONObject();
				ArrayList<String> cname= new ArrayList<String>();
				while (rs.next())
				{
					
					cname.add(rs.getString("name"));
//					String lname = rs.getString("U.lastname");
//					String name = fname +" "+ lname;
				}
				return cname;
			
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
		
		@Override
		public void insertCircles(String name, int uid) throws IllegalArgumentException {
			
			try {
				Statement statement = connection.createStatement();
				int newid=0;
				
			if(	statement.execute("select max(CircleID) from usercircles ;")){
					ResultSet rs = statement.getResultSet(); 
					
					while (rs.next()){
					int maxid =rs.getInt(1);
					newid= maxid+1;
					}
			}
			statement.execute("INSERT INTO circles (cid,name) VALUES (" + newid + ","+ "'" + name + "');");
			statement.execute("INSERT INTO usercircles (userid, circleid) VALUES (" + uid  + ","+ "'" + newid + "');");
					
			} 
			
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		
		
		
		public ArrayList<String> insertFriends(int uid,String name ) throws IllegalArgumentException {
			ArrayList<String> friendsname= new ArrayList<String>();
			try {
				Statement statement = connection.createStatement();
				int newid=0;
				
			if(	statement.execute("select distinct firstname,lastname from friends, users where friendsID in(select friendsID from circlefriends where circleID in (select circleid from usercircles uc,circles c where uc.circleid=c.cid and userID ="+uid+" and name='"+name+"')) and users.userID = friends.userID;")){
					ResultSet rs = statement.getResultSet(); 
				
					while (rs.next())
					{
						String fname = rs.getString("firstname");
						String lname = rs.getString("lastname");
    					String uname = fname +" "+ lname;
						friendsname.add(uname);
//						
					}
					return friendsname;
				
			}
					
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return friendsname;
		
}
		
		
		@Override
		public void delCir(String name, int uid) throws IllegalArgumentException {
			
			try {
				int circid=0;
				Statement statement = connection.createStatement();
				
				statement.execute("SELECT cid as cd FROM circles WHERE CID IN( select circleId from usercircles where userid="+uid+") AND name='"+name+"';");
					ResultSet rs = statement.getResultSet(); 
					
					while (rs.next()){
					circid =rs.getInt("cd");
					}
			
				statement.execute("delete from  circles where cid =" + circid+ ";");
			}
				
			
			catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		
		
}
